/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import model.ConnectionFactory;
import model.beans.Cliente;
import model.beans.Consulta;
import model.beans.Horario;
import model.beans.TipoConsulta;

/**
 *
 * @author Carol
 */
public class ConsultaDAO {
    private Connection con;
    private PreparedStatement stmtInserir;
    private PreparedStatement stmtListar;
    private PreparedStatement stmtListarAdmin;
    private PreparedStatement stmtDeletar;  
    private PreparedStatement stmtBuscarPorCodigo;
    private PreparedStatement stmtAtualizar;
    private PreparedStatement stmtExcluir;

    public ConsultaDAO() throws Exception {
        con = ConnectionFactory.getConnection();
    }

    public ConsultaDAO(Connection con) throws Exception {
        this.con = con;
    }
    
    public void inserirConsulta(Consulta consulta, int idUs) throws Exception {
        stmtInserir = con.prepareStatement("INSERT INTO consulta(data, obs, idCliente, idTpCons, idHorario, idUsuario) VALUES(?,?,?,?,?,?)");
        
        stmtInserir.setDate(1, new java.sql.Date(consulta.getData().getTime()));
        stmtInserir.setString(2, consulta.getObs());
        stmtInserir.setInt(3, consulta.getCliente().getId());
        stmtInserir.setInt(4, consulta.getTpCons().getIdTpCons());
        stmtInserir.setInt(5, consulta.getHorario().getIdHorario());
        stmtInserir.setInt(6, idUs);
        stmtInserir.executeUpdate();
    }
    
        public void atualizar(Consulta consulta) throws Exception {
        stmtAtualizar = con.prepareStatement("UPDATE consulta SET data = ?, obs = ?, idCliente = ?, idTpCons = ?, idHorario = ? WHERE idConsulta = ?");
        
        stmtAtualizar.setDate(1, new java.sql.Date(consulta.getData().getTime()));
        stmtAtualizar.setString(2, consulta.getObs());
        stmtAtualizar.setInt(3, consulta.getCliente().getId());
        stmtAtualizar.setInt(4, consulta.getTpCons().getIdTpCons());
        stmtAtualizar.setInt(5, consulta.getHorario().getIdHorario());
        stmtAtualizar.setInt(6, consulta.getIdConsulta());
        stmtAtualizar.executeUpdate();
    }
        
       public void  excluir(int id) throws Exception{
           stmtExcluir = con.prepareStatement("DELETE FROM consulta WHERE idConsulta = ?");
           stmtExcluir.setInt(1, id);
           
           stmtExcluir.executeUpdate();
       }
        
    public List<Consulta> listarConsulta(int idUsu) throws SQLException{
        
        stmtListar = con.prepareStatement("SELECT cs.idConsulta, cs.data, cs.obs, cs.idCliente, cs.idTpCons, cs.idHorario, cs.idUsuario, cl.nome as cliente, tp.nome as tipocons, tp.duracao as duracao, hr.hora as hora\n" +
                                          "FROM consulta cs\n" +
                                          "INNER JOIN cliente cl\n" +
                                          "	ON cs.idCliente = cl.idCliente\n" +
                                          "INNER JOIN tipoconsulta tp\n" +
                                          "	ON cs.idTpCons = tp.idTpCons\n" +
                                          "INNER JOIN horario hr\n" +
                                          "	ON cs.idHorario = hr.idHorario\n" + 
                                          "WHERE cs.idUsuario = ?");
        
        stmtListar.setInt(1, idUsu);
        ResultSet rs = stmtListar.executeQuery();
        List<Consulta> listaConsulta = new ArrayList();
        while(rs.next()){
            Cliente cliente = new Cliente(rs.getInt("idCliente"), rs.getString("cliente"));
            TipoConsulta tpCons = new TipoConsulta(rs.getInt("idTpCons"), rs.getString("tipocons"), rs.getString("duracao"));
            Horario horario = new Horario(rs.getInt("idHorario"), rs.getTime("hora"));
            Consulta consulta = new Consulta(rs.getInt("idConsulta"), rs.getDate("data"), cliente, tpCons, horario);
            
            listaConsulta.add(consulta);
        }
        return listaConsulta;          
    }
    
        public List<Consulta> listarConsultaAdmin() throws SQLException{
        stmtListarAdmin = con.prepareStatement("SELECT cs.idConsulta, cs.data, cs.obs, cs.idCliente, cs.idTpCons, cs.idHorario, cs.idUsuario, cl.nome as cliente, tp.nome as tipocons, tp.duracao as duracao, hr.hora as hora\n" +
                                          "FROM consulta cs\n" +
                                          "INNER JOIN cliente cl\n" +
                                          "	ON cs.idCliente = cl.idCliente\n" +
                                          "INNER JOIN tipoconsulta tp\n" +
                                          "	ON cs.idTpCons = tp.idTpCons\n" +
                                          "INNER JOIN horario hr\n" +
                                          "	ON cs.idHorario = hr.idHorario");
        
        ResultSet rs = stmtListarAdmin.executeQuery();
        List<Consulta> listaConsulta = new ArrayList();
        while(rs.next()){
            Cliente cliente = new Cliente(rs.getInt("idCliente"), rs.getString("cliente"));
            TipoConsulta tpCons = new TipoConsulta(rs.getInt("idTpCons"), rs.getString("tipocons"), rs.getString("duracao"));
            Horario horario = new Horario(rs.getInt("idHorario"), rs.getTime("hora"));
            Consulta consulta = new Consulta(rs.getInt("idConsulta"), rs.getDate("data"), cliente, tpCons, horario);
            
            listaConsulta.add(consulta);
        }
        return listaConsulta;          
    }
            
            
    public void deletarConsulta(int idConsulta) throws Exception{
        stmtDeletar = con.prepareStatement ("DELETE FROM consulta WHERE idConsulta = ?");
        
        stmtDeletar.setInt(1, idConsulta);
    	stmtDeletar.executeUpdate();
    }
    
    public Consulta buscarPorCodigo(int idCons) throws SQLException, ParseException{
        stmtBuscarPorCodigo = con.prepareStatement("SELECT cs.idConsulta, cs.data, cs.obs, cs.idCliente, cs.idTpCOns, cs.idHorario, cs.idUsuario, cl.nome as cliente, tp.nome as tipo, hr.hora as hora\n" +
                                                    "FROM consulta cs\n" +
                                                    "INNER JOIN cliente cl ON cs.idCliente = cl.idCliente\n" +
                                                    "INNER JOIN tipoconsulta tp ON cs.idTpCons = tp.idTpCons\n" +
                                                    "INNER JOIN horario hr ON cs.idHorario = hr.idHorario\n" +
                                                    "WHERE cs.idConsulta = ?;");
        Consulta consulta = new Consulta();
        stmtBuscarPorCodigo.setInt(1, idCons);
        ResultSet rs = stmtBuscarPorCodigo.executeQuery();
         if(rs.next()){
            java.util.Date data =(Date)rs.getDate("data");
            
            Cliente cliente = new Cliente(rs.getInt("idCliente"), rs.getString("cliente"));
            TipoConsulta tp = new TipoConsulta(rs.getInt("idTpCons"), rs.getString("tipo"));
            Horario hr = new Horario(rs.getInt("idHorario"), rs.getTime("hora"));
            
            consulta.setIdConsulta(rs.getInt("idConsulta"));
            consulta.setData(data);
            consulta.setObs(rs.getString("obs"));
            consulta.setCliente(cliente);
            consulta.setTpCons(tp);
            consulta.setHorario(hr);

         }
        stmtBuscarPorCodigo.close();
        return consulta;
    }
    
    
    
    @Override
    public void finalize() throws Throwable{
        super.finalize();
        stmtInserir.close();
        stmtListar.close();
        stmtListarAdmin.close();
        stmtDeletar.close();
        stmtAtualizar.close();
        stmtExcluir.close();
        con.close();
    }
}
